﻿/*
This library is free software; you can redistribute it and/or
modify it under the terms of the GNU Lesser General Public
License as published by the Free Software Foundation; either
version 2.1 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public
License along with this library; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
*/

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Data.SQLite;
using System.Data.Common;
using FirebirdSql.Data.FirebirdClient;
using System.IO;

namespace IvyOrm.Test
{
    [TestClass]
    [DeploymentItem("x86")] //SQLite
    public class ExecuteCommandExtensionTest
    {
        [TestMethod]
        public void ExecuteCommandExtension_ArgumentExceptions_Test()
        {
            DbConnection nullConnection = null;
            DbTransaction nullTransaction = null;
            DbTransaction badTransaction = new BadDbTransaction();

            Action<Action> throwsArgumentException = (action) =>
            {
                try
                {
                    action();
                    Assert.Fail();
                }
                catch (ArgumentException)
                { }
            };

            throwsArgumentException(() => ExecuteCommandExtension.ExecuteNonQuery(nullConnection, ""));
            throwsArgumentException(() => ExecuteCommandExtension.ExecuteNonQuery(nullTransaction, ""));
            throwsArgumentException(() => ExecuteCommandExtension.ExecuteNonQuery(badTransaction, ""));

            throwsArgumentException(() => ExecuteCommandExtension.ExecuteReader(nullConnection, ""));
            throwsArgumentException(() => ExecuteCommandExtension.ExecuteReader(nullTransaction, ""));
            throwsArgumentException(() => ExecuteCommandExtension.ExecuteReader(badTransaction, ""));

            throwsArgumentException(() => ExecuteCommandExtension.ExecuteReader(nullConnection, System.Data.CommandBehavior.SingleRow, ""));
            throwsArgumentException(() => ExecuteCommandExtension.ExecuteReader(nullTransaction, System.Data.CommandBehavior.SingleRow, ""));
            throwsArgumentException(() => ExecuteCommandExtension.ExecuteReader(badTransaction, System.Data.CommandBehavior.SingleRow, ""));

            throwsArgumentException(() => ExecuteCommandExtension.ExecuteScalar(nullConnection, ""));
            throwsArgumentException(() => ExecuteCommandExtension.ExecuteScalar(nullTransaction, ""));
            throwsArgumentException(() => ExecuteCommandExtension.ExecuteScalar(badTransaction, ""));
        }

        [TestMethod]
        [TestCategory("SQLite")]
        public void ExecuteNonQuery_Connection_Test()
        {
            var connection = new SQLiteConnection("Data Source=:memory:;Version=3;New=True;");
            connection.Open();
            connection.ExecuteNonQuery("create table SingleColumnTable (ColumnA TEXT)");
            connection.ExecuteNonQuery("insert into SingleColumnTable (ColumnA) values (@0)", "Row1");

            using (var command = connection.CreateCommand())
            {
                command.CommandText = "select ColumnA from SingleColumnTable";
                Assert.AreEqual("Row1", command.ExecuteScalar());
            }
        }
        [TestMethod]
        [TestCategory("SQLite")]
        public void ExecuteNonQuery_Transaction_Test()
        {
            using (var connection = new SQLiteConnection("Data Source=:memory:;Version=3;New=True;"))
            {
                connection.Open();
                connection.ExecuteNonQuery("create table SingleColumnTable (ColumnA TEXT)");
                using (var transaction = connection.BeginTransaction())
                {
                    transaction.ExecuteNonQuery("insert into SingleColumnTable (ColumnA) values (@0)", "Row1");

                    using (var command = connection.CreateCommand())
                    {
                        command.Transaction = transaction;
                        command.CommandText = "select ColumnA from SingleColumnTable";
                        Assert.AreEqual("Row1", command.ExecuteScalar());
                    }

                    transaction.Rollback();

                    using (var command = connection.CreateCommand())
                    {
                        command.CommandText = "select ColumnA from SingleColumnTable";
                        var rolledBackResults = command.ExecuteScalar();
                        Assert.IsTrue(rolledBackResults == null || rolledBackResults == DBNull.Value);
                    }
                }
            }
        }

        [TestMethod]
        [TestCategory("SQLite")]
        public void ExecuteReader_Connection_Test()
        {
            using (var connection = new SQLiteConnection("Data Source=:memory:;Version=3;New=True;"))
            {
                connection.Open();
                connection.ExecuteNonQuery("create table SingleColumnTable (ColumnA TEXT)");
                connection.ExecuteNonQuery("insert into SingleColumnTable (ColumnA) values ('Row1')");

                using (var reader = connection.ExecuteReader("select * from SingleColumnTable where ColumnA=@0", "Row1"))
                {
                    Assert.IsTrue(reader.Read());
                    Assert.AreEqual("Row1", reader["ColumnA"]);
                }

                using (var reader = connection.ExecuteReader(System.Data.CommandBehavior.SingleRow, "select * from SingleColumnTable where ColumnA=@0", "Row1"))
                {
                    Assert.IsTrue(reader.Read());
                    Assert.AreEqual("Row1", reader["ColumnA"]);
                }
            }
        }
        [TestMethod]
        [TestCategory("SQLite")]
        public void ExecuteReader_Transaction_Test()
        {
            using (var connection = new SQLiteConnection("Data Source=:memory:;Version=3;New=True;"))
            {
                connection.Open();
                connection.ExecuteNonQuery("create table SingleColumnTable (ColumnA TEXT)");

                using (var transaction = connection.BeginTransaction())
                {
                    using (var command = connection.CreateCommand())
                    {
                        command.Transaction = transaction;
                        command.CommandText = "insert into SingleColumnTable (ColumnA) values ('Row1')";
                        command.ExecuteNonQuery();
                    }

                    using (var reader = transaction.ExecuteReader("select * from SingleColumnTable where ColumnA=@0", "Row1"))
                    {
                        Assert.IsTrue(reader.Read());
                        Assert.AreEqual("Row1", reader["ColumnA"]);
                    }

                    using (var reader = transaction.ExecuteReader(System.Data.CommandBehavior.SingleRow, "select * from SingleColumnTable where ColumnA=@0", "Row1"))
                    {
                        Assert.IsTrue(reader.Read());
                        Assert.AreEqual("Row1", reader["ColumnA"]);
                    }
                }
            }
        }

        [TestMethod]
        [TestCategory("Firebird")]
        public void ExecuteReader_NotSupported_Firebird()
        {
            var parameters = new FbConnectionStringBuilder();
            parameters.Add("User", "SYSDBA");
            parameters.Add("Password", "password");
            parameters.Database = Path.GetFullPath(Path.ChangeExtension(Path.GetRandomFileName(), ".fdb"));
            parameters.ServerType = FbServerType.Embedded;
            FbConnection.CreateDatabase(parameters.ConnectionString);
            using (var connection = new FbConnection(parameters.ConnectionString))
            {
                connection.Open();

                AssertB.ThrowsException<NotSupportedException>(
                    () => connection.ExecuteReader(System.Data.CommandBehavior.Default, ""),
                    (e) => e.Message.Contains("02CB7395"));

                AssertB.ThrowsException<NotSupportedException>(
                    () => connection.ExecuteReader(""),
                    (e) => e.Message.Contains("02CB7395"));

                using (var transaction = connection.BeginTransaction())
                {
                    AssertB.ThrowsException<NotSupportedException>(
                        () => transaction.ExecuteReader(System.Data.CommandBehavior.Default, ""),
                        (e) => e.Message.Contains("02CB7395"));

                    AssertB.ThrowsException<NotSupportedException>(
                        () => transaction.ExecuteReader(""),
                        (e) => e.Message.Contains("02CB7395"));
                }


            }
        }


        [TestMethod]
        [TestCategory("SQLite")]
        public void ExecuteScalar_Connection_Test()
        {
            using (var connection = new SQLiteConnection("Data Source=:memory:;Version=3;New=True;"))
            {
                connection.Open();
                connection.ExecuteNonQuery("create table SingleColumnTable (ColumnA TEXT)");

                //using (var transaction = connection.BeginTransaction())
                {
                    using (var command = connection.CreateCommand())
                    {
                        //command.Transaction = transaction;
                        command.CommandText = "insert into SingleColumnTable (ColumnA) values ('Row1')";
                        command.ExecuteNonQuery();
                    }


                    Assert.AreEqual("Row1", connection.ExecuteScalar("select * from SingleColumnTable where ColumnA=@0", "Row1"));
                }
            }
        }
        
        [TestMethod]
        [TestCategory("SQLite")]
        public void ExecuteScalar_Transaction_Test()
        {
            using (var connection = new SQLiteConnection("Data Source=:memory:;Version=3;New=True;"))
            {
                connection.Open();
                connection.ExecuteNonQuery("create table SingleColumnTable (ColumnA TEXT)");

                using (var transaction = connection.BeginTransaction())
                {
                    using (var command = connection.CreateCommand())
                    {
                        command.Transaction = transaction;
                        command.CommandText = "insert into SingleColumnTable (ColumnA) values ('Row1')";
                        command.ExecuteNonQuery();
                    }


                    Assert.AreEqual("Row1", transaction.ExecuteScalar("select * from SingleColumnTable where ColumnA=@0", "Row1"));
                }
            }
        }

        [TestMethod]
        [TestCategory("SQLite")]
        public void ExecuteNonQuery_EscapedParameter()
        {
            using (var connection = new SQLiteConnection("Data Source=:memory:;Version=3;New=True;"))
            {
                connection.Open();
                connection.ExecuteNonQuery("create table SingleColumnTable (ColumnA TEXT)");

                connection.ExecuteNonQuery("insert into SingleColumnTable (ColumnA) values ('@@')", "Row1");

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "select ColumnA from SingleColumnTable";
                    Assert.AreEqual("@", command.ExecuteScalar());
                }
            }
        }

        [TestMethod]
        [TestCategory("SQLite")]
        public void ExecuteNonQuery_FormatException_Test()
        {
            var connection = new SQLiteConnection("Data Source=:memory:;Version=3;New=True;");
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "create table SingleColumnTable (ColumnA TEXT)";
                command.ExecuteNonQuery();
            }

            AssertB.ThrowsException<FormatException>(() =>
                {
                    connection.ExecuteNonQuery("insert into SingleColumnTable (ColumnA) values ('@A')", "Param0");
                });

            AssertB.ThrowsException<FormatException>(() =>
                {
                    connection.ExecuteNonQuery("insert into SingleColumnTable (ColumnA) values (@1)", "Param0");
                });
        }
    }
}
